﻿Public Class SAP_Functions
    Private Function CalculateStdDev(values As IEnumerable(Of Double)) As Double
        Dim ret As Double = 0
        If values.Count() > 0 Then
            'Compute the Average      
            Dim avg As Double = values.Average()
            'Perform the Sum of (value-avg)_2_2      
            Dim sum As Double = values.Sum(Function(d) Math.Pow(d - avg, 2))
            'Put it all together      
            ret = Math.Sqrt((sum) / (values.Count() - 1))
        End If
        Return ret
    End Function
    Public Function ConvertRS2DT(ByVal RS As SAPbobsCOM.Recordset) As DataSet
        Dim dtTable As New DataSet
        dtTable.Tables.Add()
        Dim NewCol As DataColumn
        Dim NewRow As DataRow
        Dim ColCount As Integer
        Try
            For ColCount = 0 To RS.Fields.Count - 1
                Dim dataType As String = "System."
                Select Case RS.Fields.Item(ColCount).Type
                    Case SAPbobsCOM.BoFieldTypes.db_Alpha
                        dataType = dataType & "String"
                    Case SAPbobsCOM.BoFieldTypes.db_Date
                        dataType = dataType & "DateTime"
                    Case SAPbobsCOM.BoFieldTypes.db_Float
                        dataType = dataType & "Double"
                    Case SAPbobsCOM.BoFieldTypes.db_Memo
                        dataType = dataType & "String"
                    Case SAPbobsCOM.BoFieldTypes.db_Numeric
                        dataType = dataType & "Decimal"
                    Case Else
                        dataType = dataType & "String"
                End Select

                NewCol = New DataColumn(RS.Fields.Item(ColCount).Name, System.Type.GetType(dataType))
                dtTable.Tables(0).Columns.Add(NewCol)
            Next
            RS.MoveFirst()
            Do Until RS.EoF

                NewRow = dtTable.Tables(0).NewRow
                'populate each column in the row we're creating
                For ColCount = 0 To RS.Fields.Count - 1

                    NewRow.Item(RS.Fields.Item(ColCount).Name) = RS.Fields.Item(ColCount).Value

                Next

                'Add the row to the datatable
                dtTable.Tables(0).Rows.Add(NewRow)

                RS.MoveNext()
            Loop
            Return dtTable
        Catch ex As Exception
            MsgBox(ex.ToString & Chr(10) & "Error converting SAP Recordset to DataTable", MsgBoxStyle.Exclamation)
            Return Nothing
        End Try
    End Function
    Public Function GetGopyFromTo(ByVal Type As Integer, ByVal ObjType As String) As DataSet
        Dim ds = New DataSet
        ds.Tables.Add()
        ds.Tables(0).Columns.Add("Code", GetType(String))
        ds.Tables(0).Columns.Add("Name", GetType(String))
        Dim dr As DataRow

        Select Case Type
            Case 1 ' Copy To
                Select Case ObjType
                    Case "22"
                        dr = ds.Tables(0).NewRow
                        dr("Code") = "AA"
                        dr("Name") = "GRPO"
                        ds.Tables(0).Rows.Add(dr)

                        dr = ds.Tables(0).NewRow
                        dr("Code") = "AA"
                        dr("Name") = "AP Invoice"
                        ds.Tables(0).Rows.Add(dr)
                End Select
            Case 2 ' Copy From
                Select Case ObjType
                    Case "22"
                        dr = ds.Tables(0).NewRow
                        dr("Code") = "AA"
                        dr("Name") = "Purchase Quotation"
                        ds.Tables(0).Rows.Add(dr)
                End Select
        End Select
        Return ds
    End Function
    Public Function GetMaxDocEntry(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ID_Department As String, ByVal ObjType As String, ByVal TableName As String, ByVal KeyName As String) As String
        Try
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery("select MAX(" + KeyName + ") DocKey from " + TableName + " where ObjType = " + ObjType + " and U_DealerID = '" + ID_Dealer + "' and U_Department = '" + ID_Department + "' ")
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item("DocKey").Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Function ReturnMessage(ByVal ErrCode As Integer, ByVal ErrMsg As String) As DataSet
        Dim dtJE = New DataSet
        dtJE.Tables.Add()
        dtJE.Tables(0).Columns.Add("ErrCode", GetType(Integer))
        dtJE.Tables(0).Columns.Add("ErrMsg", GetType(String))

        Dim dr As DataRow
        dr = dtJE.Tables(0).NewRow
        dr("ErrCode") = ErrCode
        dr("ErrMsg") = ErrMsg
        dtJE.Tables(0).Rows.Add(dr)
        Return dtJE
    End Function

    Public Function GetDefaultWarehouse(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ID_Department As String) As String
        Dim str As String = String.Empty
        Try
            If dbType = PublicVariable.VSM Then
                str = "select top(1) WhsCode from OWHS where U_Department = '" + ID_Department + "'"
            Else
                str = "select top(1) WhsCode from OWHS where U_Department = '" + ID_Department + "' and U_DealerID = '" + ID_Dealer + "'"
            End If

            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery(str)
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item("WhsCode").Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Function GetItemName(ByVal dbType As String, ByVal ID_Dealer As String, ByVal itemCode As String, ByVal key As String) As String
        Dim str As String = String.Empty
        Try
            str = "SELECT * FROM OITM WHERE ItemCode = '" + itemCode + "'"
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery(str)
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item(key).Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Function GetModel(ByVal dbType As String, ByVal ID_Dealer As String, ByVal itemCode As String) As String
        Dim str As String = String.Empty
        Try
            str = "SELECT ItemName,U_Model FROM OITM WHERE ItemCode = '" + itemCode + "'"
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery(str)
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item("U_Model").Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Function GetRefNo(ByVal dbType As String, ByVal strSQLCommand As String) As String
        Try
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery(strSQLCommand)
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item("NumAtCard").Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function

    Public Function GetAccountName(ByVal dbType As String, ByVal strSQLCommand As String) As String
        Try
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If

            ors.DoQuery(strSQLCommand)
            If ors.RecordCount = 1 Then
                Return ors.Fields.Item("AcctName").Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function

    Public Function GetSingleValueBySqlCommand(ByVal dbType As String, ByVal strSQLCommand As String, ByVal KeyName As String) As String
        Try
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If

            ors.DoQuery(strSQLCommand)
            If ors.RecordCount >= 1 Then
                Return ors.Fields.Item(KeyName).Value.ToString
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function

    Public Function GetMasterListBySqlCommand(ByVal dbType As String, ByVal strSQLCommand As String) As DataSet
        Try
            Dim ors As SAPbobsCOM.Recordset
            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If

            ors.DoQuery(strSQLCommand)
            Return ConvertRS2DT(ors)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function GetDefaultTaxCode(ByVal dbType As String, ByVal ID_Dealer As String, ByVal ItemCode As String, ByVal CardCode As String) As DataSet
        Dim str As String = ""
        Try
            str = "select Code,Name,Rate from ovtg where Code=("
            str = str + " select case when (select CardType from ocrd where cardcode='" + CardCode + "')='C' then VatGourpSa else VatGroupPu end TaxCode from OITM where ItemCode='" + ItemCode + "'"
            str = str + " )"

            Dim ors As SAPbobsCOM.Recordset

            If (PublicVariable.VSM = dbType) Then
                ors = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            Else
                ors = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            End If
            ors.DoQuery(str)
            Return ConvertRS2DT(ors)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function GetPriceAfterDiscount(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal itemCode As String, ByVal amount As Single, ByVal refDate As Date) As Double
        Dim connect As New Connection()
        Dim vObj As SAPbobsCOM.SBObob
        Dim rs As SAPbobsCOM.Recordset

        connect.setDB(dbType)

        If (PublicVariable.VSM = dbType) Then
            vObj = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
            rs = PublicVariable.mVSMDataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            rs = vObj.GetItemPrice(cardCode, itemCode, amount, refDate)
        Else
            vObj = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoBridge)
            rs = PublicVariable.mDEADataComapny.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
            rs = vObj.GetItemPrice(cardCode, itemCode, amount, refDate)
        End If

        Return rs.Fields.Item("Price").Value
    End Function

    Public Function GetGrossPrice(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal itemCode As String) As Double
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim dt As DataTable
        Dim str As String = ""
        str = " select isnull(t0.Price,0) GrossPrice from ITM1 T0 "
        str = str & " join OCRD T1 on T0.PriceList=T1.ListNum "
        str = str & " where T0.ItemCode='" + itemCode + "' and T1.CardCode='" + cardCode + "'"
        dt = connect.ObjectGetAll_Query(str, dbType).Tables(0)
        If dt.Rows.Count = 0 Then
            Return 0
        Else
            Return dt.Rows(0).Item("GrossPrice")
        End If
    End Function

    Public Function GetWholeSalePrice(ByVal dbType As String, ByVal ID_Dealer As String, ByVal itemCode As String) As Double
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim dt As DataTable
        Dim str As String = ""
        str = " select isnull(T0.Price,0) WholeSalePrice from ITM1 T0 "
        str = str & " where T0.ItemCode='" + itemCode + "' and T0.PriceList= 5 "
        dt = connect.ObjectGetAll_Query(str, dbType).Tables(0)
        If dt.Rows.Count = 0 Then
            Return 0
        Else
            Return dt.Rows(0).Item("WholeSalePrice")
        End If
    End Function

    Public Function GetPartsGrossPrice(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal itemCode As String, ByVal priceType As String) As Double
        Dim connect As New Connection()
        connect.setDB(dbType)
        Dim dt As DataTable
        Dim str As String = ""
        str += " SELECT ISNULL(t0.Price,0) GrossPrice FROM ITM1 T0 "
        str += " JOIN OITM T1 ON T0.ItemCode = T1.ItemCode"
        str += " JOIN OPLN T2 ON T0.PriceList = T2.ListNum"
        str += " WHERE T0.ItemCode='" + itemCode + "' AND T2.ListNum = " + priceType + ""
        ' AND T1.CardCode='" + cardCode + "'"
        dt = connect.ObjectGetAll_Query(str, dbType).Tables(0)
        If dt.Rows.Count = 0 Then
            Return 0
        Else
            Return dt.Rows(0).Item("GrossPrice")
        End If
    End Function

    Public Function GetDefaultLineInfo(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal itemCode As String, _
                                       ByVal amount As Single, ByVal refDate As Date, ByVal ID_Department As String) As DataSet
        Dim ds = New DataSet
        ds.Tables.Add()
        ds.Tables(0).Columns.Add("UnitPrice", GetType(Double))
        ds.Tables(0).Columns.Add("Discount", GetType(Double))
        ds.Tables(0).Columns.Add("PriceAfDi", GetType(Double))
        ds.Tables(0).Columns.Add("WhsCode", GetType(String))
        ds.Tables(0).Columns.Add("TaxCode", GetType(String))
        ds.Tables(0).Columns.Add("TaxRate", GetType(Double))
        ds.Tables(0).Columns.Add("WholeSalePrice", GetType(Double)) 'thangnv added on 20140729 (WholeSalePrice: Giá bán sĩ)

        Dim GrossPrice As Double = GetGrossPrice(dbType, ID_Dealer, cardCode, itemCode)
        Dim NetPrice As Double = GetPriceAfterDiscount(dbType, ID_Dealer, cardCode, itemCode, amount, refDate)
        Dim WholeSalePrice As Double = GetWholeSalePrice(dbType, ID_Dealer, itemCode)
        Dim Discount As Double = 0
        Dim WhsCode As String = GetDefaultWarehouse(dbType, ID_Dealer, ID_Department)
        If WhsCode = "" Then WhsCode = "01"
        Dim TaxCode As String = ""
        Dim TaxRate As Double = 0
        Dim dstax As DataSet = GetDefaultTaxCode(dbType, ID_Dealer, itemCode, cardCode)
        If Not IsNothing(dstax) Then
            If dstax.Tables.Count > 0 Then
                If dstax.Tables(0).Rows.Count > 0 Then
                    TaxCode = dstax.Tables(0).Rows(0).Item("Code").ToString
                    TaxRate = dstax.Tables(0).Rows(0).Item("Rate").ToString
                End If
            End If
        End If
        If GrossPrice = 0 Then
            Discount = 0
        Else
            Discount = (GrossPrice - NetPrice) * 100 / GrossPrice
        End If

        Dim dr As DataRow
        dr = ds.Tables(0).NewRow
        dr("UnitPrice") = GrossPrice
        dr("Discount") = Discount
        dr("PriceAfDi") = NetPrice
        dr("WhsCode") = WhsCode
        dr("TaxCode") = TaxCode
        dr("TaxRate") = TaxRate
        dr("WholeSalePrice") = WholeSalePrice
        ds.Tables(0).Rows.Add(dr)

        Return ds
    End Function
    Public Function GetPartsDefaultLineInfo(ByVal dbType As String, ByVal ID_Dealer As String, ByVal cardCode As String, ByVal itemCode As String, _
                                       ByVal amount As Single, ByVal refDate As Date, ByVal ID_Department As String, ByVal priceType As String) As DataSet
        Dim ds = New DataSet
        ds.Tables.Add()
        ds.Tables(0).Columns.Add("UnitPrice", GetType(Double))
        ds.Tables(0).Columns.Add("Discount", GetType(Double))
        ds.Tables(0).Columns.Add("PriceAfDi", GetType(Double))
        ds.Tables(0).Columns.Add("WhsCode", GetType(String))
        ds.Tables(0).Columns.Add("TaxCode", GetType(String))
        ds.Tables(0).Columns.Add("TaxRate", GetType(Double))
        ds.Tables(0).Columns.Add("ItemName", GetType(String))
        ds.Tables(0).Columns.Add("U_Model", GetType(String))
        ds.Tables(0).Columns.Add("UomCode", GetType(String))

        Dim GrossPrice As Double = GetPartsGrossPrice(dbType, ID_Dealer, cardCode, itemCode, priceType)
        Dim NetPrice As Double = GetPriceAfterDiscount(dbType, ID_Dealer, cardCode, itemCode, amount, refDate)
        Dim Discount As Double = 0
        Dim WhsCode As String = GetDefaultWarehouse(dbType, ID_Dealer, ID_Department)
        If WhsCode = "" Then WhsCode = "01"
        Dim TaxCode As String = ""
        Dim TaxRate As Double = 0
        Dim dstax As DataSet = GetDefaultTaxCode(dbType, ID_Dealer, itemCode, cardCode)
        If Not IsNothing(dstax) Then
            If dstax.Tables.Count > 0 Then
                If dstax.Tables(0).Rows.Count > 0 Then
                    TaxCode = dstax.Tables(0).Rows(0).Item("Code").ToString
                    TaxRate = dstax.Tables(0).Rows(0).Item("Rate").ToString
                End If
            End If
        End If
        If GrossPrice = 0 Then
            Discount = 0
        Else
            Discount = (GrossPrice - NetPrice) * 100 / GrossPrice
        End If
        Dim ItemName As String = GetItemName(dbType, ID_Dealer, itemCode, "ItemName")
        Dim UomCode As String = GetItemName(dbType, ID_Dealer, itemCode, "InvntryUom")
        Dim dr As DataRow
        dr = ds.Tables(0).NewRow
        dr("UnitPrice") = GrossPrice
        dr("Discount") = Discount
        dr("PriceAfDi") = NetPrice
        dr("WhsCode") = WhsCode
        dr("TaxCode") = TaxCode
        dr("TaxRate") = TaxRate
        dr("ItemName") = ItemName
        dr("UomCode") = UomCode
        dr("U_Model") = GetModel(dbType, ID_Dealer, itemCode)
        ds.Tables(0).Rows.Add(dr)

        Return ds
    End Function

    Public Function GetWarehouseByDealerDepartment(ByVal dbType As String, ByVal ID_Dealer As String, ByVal Department As String) As DataSet
        Dim connect As New Connection()
        connect.setDB(dbType)

        Dim ds = New DataSet("OWHS")
        Dim strCondition As String = String.Empty
        strCondition = " where U_Department = '" & Department & "'"
        If String.IsNullOrEmpty(ID_Dealer) = False And ID_Dealer.CompareTo(PublicVariable.VSM) <> 0 Then
            strCondition = strCondition + " and U_DealerID = '" & ID_Dealer & "'"
        End If
        ds = connect.ObjectGetAll_Query("Select WhsCode, WhsName from OWHS " & strCondition & " order by WhsCode", dbType)

        Return ds
    End Function

End Class
